from sqlalchemy import Column, Integer, String, DateTime, Date, ForeignKey, LargeBinary, Text, BIGINT, SMALLINT, UniqueConstraint, Index, Boolean
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy.sql import func
from datetime import datetime

Base = declarative_base()

class Merchant(Base):
    """商户表模型"""
    __tablename__ = "merchant"
    
    id = Column(BIGINT, primary_key=True, autoincrement=True, comment="商户ID")
    name = Column(String(100), nullable=False, comment="商户名称")
    code = Column(String(50), nullable=False, unique=True, comment="商户编码")
    contact_person = Column(String(50), comment="联系人")
    contact_phone = Column(String(20), comment="联系电话")
    address = Column(String(255), comment="地址")
    status = Column(SMALLINT, nullable=False, default=1, comment="状态：0-禁用，1-启用")
    remark = Column(String(500), comment="备注信息")
    created_at = Column(DateTime, nullable=False, default=func.now(), comment="创建时间")
    updated_at = Column(DateTime, nullable=False, default=func.now(), onupdate=func.now(), comment="更新时间")
    
    # 关联关系
    reports = relationship("Report", back_populates="merchant")
    files = relationship("FileUpload", back_populates="merchant")

class Report(Base):
    """报告表模型"""
    __tablename__ = "report"
    
    id = Column(BIGINT, primary_key=True, autoincrement=True, comment="报告ID")
    merchant_id = Column(BIGINT, ForeignKey("merchant.id"), nullable=False, comment="商户ID")
    report_code = Column(String(50), nullable=False, unique=True, comment="报告编号")
    report_name = Column(String(100), nullable=False, comment="报告名称")
    report_date = Column(Date, nullable=False, comment="报告日期")
    status = Column(SMALLINT, nullable=False, default=0, comment="状态：0-未完成，1-部分完成，2-已完成")
    file_count = Column(SMALLINT, nullable=False, default=0, comment="已上传文件数量")
    expected_file_count = Column(SMALLINT, nullable=False, default=2, comment="预期文件数量")
    operator = Column(String(50), comment="创建人")
    remark = Column(String(500), comment="备注信息")
    created_at = Column(DateTime, nullable=False, default=func.now(), comment="创建时间")
    updated_at = Column(DateTime, nullable=False, default=func.now(), onupdate=func.now(), comment="更新时间")
    
    # 关联关系
    merchant = relationship("Merchant", back_populates="reports")
    files = relationship("FileUpload", back_populates="report")
    integrated_report = relationship("IntegratedReport", back_populates="report", uselist=False)

class FileUpload(Base):
    """文件上传表模型"""
    __tablename__ = "file_upload"
    
    id = Column(BIGINT, primary_key=True, autoincrement=True, comment="文件ID")
    report_id = Column(BIGINT, ForeignKey("report.id"), nullable=True, comment="关联的报告ID")
    merchant_id = Column(BIGINT, ForeignKey("merchant.id"), nullable=False, comment="商户ID")
    file_name = Column(String(255), nullable=False, comment="文件名称")
    file_path = Column(String(500), comment="文件路径(外部存储时使用)")
    file_size = Column(BIGINT, nullable=False, comment="文件大小(字节)")
    file_type = Column(SMALLINT, nullable=False, comment="文件类型：1-抖音来客，2-抖音生意经")
    file_format = Column(String(20), nullable=False, comment="文件格式(如xlsx, csv等)")
    file_content = Column(LargeBinary, comment="文件内容(KB级文件直接存储)")
    store_type = Column(SMALLINT, nullable=False, default=1, comment="存储类型：1-数据库存储，2-文件系统存储")
    upload_time = Column(DateTime, nullable=False, default=func.now(), comment="上传时间")
    status = Column(SMALLINT, nullable=False, default=1, comment="状态：0-无效，1-有效")
    process_status = Column(SMALLINT, nullable=False, default=0, comment="处理状态：0-未处理，1-处理中，2-处理完成，3-处理失败")
    process_message = Column(String(500), comment="处理结果信息")
    operator = Column(String(50), comment="上传人")
    remark = Column(String(500), comment="备注信息")
    created_at = Column(DateTime, nullable=False, default=func.now(), comment="创建时间")
    updated_at = Column(DateTime, nullable=False, default=func.now(), onupdate=func.now(), comment="更新时间")
    
    # 关联关系
    report = relationship("Report", back_populates="files")
    merchant = relationship("Merchant", back_populates="files")

class IntegratedReport(Base):
    """整合报告表模型"""
    __tablename__ = "integrated_report"
    
    id = Column(BIGINT, primary_key=True, autoincrement=True, comment="整合报告ID")
    report_id = Column(BIGINT, ForeignKey("report.id"), nullable=False, unique=True, comment="关联的报告ID")
    file_path = Column(String(500), comment="整合后的文件路径(外部存储时使用)")
    file_size = Column(BIGINT, nullable=False, comment="文件大小(字节)")
    report_content = Column(LargeBinary, comment="报告内容(KB级文件直接存储)")
    store_type = Column(SMALLINT, nullable=False, default=1, comment="存储类型：1-数据库存储，2-文件系统存储")
    file_format = Column(String(20), nullable=False, comment="文件格式(如xlsx, csv等)")
    file_name = Column(String(255), nullable=False, comment="文件名称")
    integration_time = Column(DateTime, nullable=False, default=func.now(), comment="整合时间")
    status = Column(SMALLINT, nullable=False, default=1, comment="状态：0-无效，1-有效")
    operator = Column(String(50), comment="操作人")
    remark = Column(String(500), comment="备注信息")
    created_at = Column(DateTime, nullable=False, default=func.now(), comment="创建时间")
    updated_at = Column(DateTime, nullable=False, default=func.now(), onupdate=func.now(), comment="更新时间")
    
    # 关联关系
    report = relationship("Report", back_populates="integrated_report")

class MerchantMetadata(Base):
    """商户元数据表"""
    __tablename__ = "merchant_metadata"
    
    id = Column(Integer, primary_key=True, autoincrement=True, comment="主键ID")
    merchant_id = Column(String(50), nullable=False, comment="商户ID")
    merchant_name = Column(String(100), nullable=False, comment="商户名称")
    platform_type = Column(String(50), nullable=False, comment="平台类型")
    data_table_name = Column(String(100), nullable=False, comment="数据表名")
    status = Column(Integer, default=1, comment="状态：1-有效，0-无效")
    remark = Column(String(500), comment="备注信息")
    created_at = Column(DateTime, nullable=False, default=func.now(), comment="创建时间")
    updated_at = Column(DateTime, nullable=False, default=func.now(), onupdate=func.now(), comment="更新时间")

    # 添加联合唯一约束
    __table_args__ = (
        UniqueConstraint('merchant_id', 'platform_type', name='uk_merchant_platform'),
    )

    def __init__(self, merchant_id, merchant_name, platform_type):
        self.merchant_id = merchant_id
        self.merchant_name = merchant_name
        self.platform_type = platform_type
        # 生成数据表名：使用商户名称_商户ID的格式
        self.data_table_name = f"{merchant_name}_{merchant_id}"

class Template(Base):
    """模板表模型"""
    __tablename__ = "template"
    
    id = Column(Integer, primary_key=True, autoincrement=True, comment="主键ID")
    name = Column(String(100), nullable=False, comment="模板名称")
    description = Column(String(500), nullable=True, comment="模板描述")
    is_default = Column(Boolean, nullable=False, default=False, comment="是否为默认模板")
    created_at = Column(DateTime, nullable=False, default=func.now(), comment="创建时间")
    updated_at = Column(DateTime, nullable=False, default=func.now(), onupdate=func.now(), comment="更新时间")

    # 添加索引
    __table_args__ = (
        # 模板名称唯一约束
        UniqueConstraint('name', name='uk_template_name'),
        # 默认模板唯一约束
        UniqueConstraint('is_default', name='uk_template_default'),
    )

class DataMapping(Base):
    """数据映射表模型"""
    __tablename__ = "data_mapping"
    
    id = Column(Integer, primary_key=True, autoincrement=True, comment="主键ID")
    template_id = Column(Integer, nullable=False, comment="模板ID")
    # 源数据项字段
    source_topic = Column(String(50), nullable=False, default="默认", comment="源数据主题")
    source_group_name = Column(String(100), nullable=False, comment="源数据分组名称")
    source_metric_name = Column(String(100), nullable=False, comment="源数据指标名称")
    source_metric_subitem = Column(String(50), nullable=False, comment="源数据指标子项")
    # 目标数据项字段
    target_group_title = Column(String(100), nullable=False, comment="目标分组标题")
    target_metric_name = Column(String(100), nullable=False, comment="目标指标名称")
    target_metric_path = Column(String(200), nullable=False, comment="目标指标路径，使用点分隔，如：value.productName")
    created_at = Column(DateTime, nullable=False, server_default=func.now(), comment="创建时间")
    updated_at = Column(DateTime, nullable=False, server_default=func.now(), server_onupdate=func.now(), comment="更新时间")

    # 添加索引
    __table_args__ = (
        # 源数据项唯一约束（在同一模板内）
        UniqueConstraint(
            'template_id',
            'source_topic', 
            'source_group_name', 
            'source_metric_name', 
            'source_metric_subitem',
            name='uk_source_metric'
        ),
        # 源数据项索引
        Index(
            'idx_data_mapping_source', 
            'template_id',
            'source_topic',
            'source_group_name', 
            'source_metric_name', 
            'source_metric_subitem'
        ),
        # 目标数据项索引
        Index('idx_data_mapping_target', 'template_id', 'target_group_title', 'target_metric_name'),
    ) 